package com.boss.thirteen.dao.impl;


import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.salary.constant.TableName;
import com.boss.sys.utils.SysUtils;
import com.boss.thirteen.dao.IThirteenGenerateBankDataDao;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Repository
public class ThirteenGenerateBankDataDaoImpl extends BaseDaoImpl implements IThirteenGenerateBankDataDao {

    @Override
    public List<Map<String,Object>> selectGenerateBankDataPage(PaginationDTO paginationDTO,
                                                         List<String> agencyIds,
                                                         Map<String, Object> queryForm){
        StringBuilder sb = new StringBuilder();
        List<Object> conditions = new ArrayList<>();
        sb.append(" select ")
                .append(" avy.is_bank_data_created status, avy.id, avy.agency_id agencyId, ")
                .append(" avy.agency_code agencyCode, avy.agency_name agencyName, ")
                .append(" avy.salary_year year, ifNull(sum(s1),0) realSalary,count(*) personCount ")
                .append(" from ")
                .append(TableName.SALARY_THIRTEEN_MONTH).append(" smed ")
                .append(" join ") .append(TableName.THIRTEEN_AGENCY_VERIFY).append(" avy ")
                .append(" on ").append(" smed.agency_id  = avy.agency_id  ")
                .append(" where ")
                .append(" avy.is_deleted = 0 ")
                .append(" and avy.is_end = 0 and avy.is_pay_data_created = 1 ")
                .append(" and  ").append(SysUtils.inSql("smed.agency_id", agencyIds));

        if(!CollectionUtils.isEmpty(queryForm)){

            if (!StringUtils.isEmpty(queryForm.get("province"))){
                sb.append(" and smed.mof_div_code = ? ");
                conditions.add(queryForm.get("province"));
            }
            if(!StringUtils.isEmpty(queryForm.get("salaCardBank"))){
                sb.append(" and smed.sala_card_bank=?");
                conditions.add(queryForm.get("salaCardBank"));
            }
            if(!StringUtils.isEmpty(queryForm.get("tabPane"))){
                if("0".equals(queryForm.get("tabPane")+"")){
                    sb.append(" and avy.is_bank_data_created = 0 ");
                }else if("1".equals(queryForm.get("tabPane")+"")){
                    sb.append(" and avy.is_bank_data_created = 2  ");
                }else{
                    sb.append(" and avy.is_bank_data_created = 1 ");
                }
            }
        }
        sb.append(" group by avy.is_bank_data_created , avy.id, avy.agency_id, avy.agency_code, avy.agency_name, avy.salary_year ")
                .append(" order by avy.agency_code ");
        String sql = super.boxUpSql(sb.toString());
        Object[] params = super.boxUpParam(conditions.toArray(), paginationDTO);
        return super.getjdbcTemplate().queryForList(sql, params);
    }

    @Override
    public int selectGenerateBankDataTotal(List<String> agencyIds,
                                           Map<String, Object> queryForm) {
        StringBuilder sb = new StringBuilder();
        List<Object> conditions = new ArrayList<>();
        sb.append(" select count(*) from (")
                .append(" select avy.id from ")
                .append(TableName.THIRTEEN_AGENCY_VERIFY).append(" avy ")
                .append(" join ").append(TableName.SALARY_THIRTEEN_MONTH).append(" stm ")
                .append(" on ").append(" stm.agency_id = avy.agency_id  ")
                .append(" where ")
                .append(" avy.is_deleted = 0 ")
                .append(" and avy.is_end = 0 and avy.is_pay_data_created = 1 ")
                .append(" and  ").append(SysUtils.inSql("avy.agency_id", agencyIds));
        if(!CollectionUtils.isEmpty(queryForm)){
            if (!StringUtils.isEmpty(queryForm.get("province"))){
                sb.append(" and avy.mof_div_code = ? ");
                conditions.add(queryForm.get("province"));
            }
            if(!StringUtils.isEmpty(queryForm.get("salaCardBank"))){
                sb.append(" and stm.sala_card_bank=?");
                conditions.add(queryForm.get("salaCardBank"));
            }
            if(!StringUtils.isEmpty(queryForm.get("tabPane"))){
                if("0".equals(queryForm.get("tabPane")+"")){
                    sb.append(" and avy.is_bank_data_created = 0 ");
                }else if("1".equals(queryForm.get("tabPane")+"")){
                    sb.append(" and avy.is_bank_data_created = 2  ");
                }else{
                    sb.append(" and avy.is_bank_data_created = 1 ");
                }
            }
        }else{}
        sb.append(" group by avy.agency_code,avy.id ) t");
        return super.getjdbcTemplate().queryForObject(sb.toString(), conditions.toArray(), int.class);
    }

    @Override
    public List<Map<String, Object>> getGenerateMessage(Map<String, Object> queryForm,List<String> agencyIds) {
        StringBuilder sb = new StringBuilder();
        List<Object> conditions = new ArrayList<>();
        sb.append(" select ")
                .append(" count(agencyId) agencyCount,sum(personCount) personCount,sum(realSalary) sumRealSalary")
                .append(" from ( ")
                .append(" select ")
                .append(" avy.is_bank_data_created `status`, avy.id, avy.agency_id agencyId, ")
                .append(" avy.agency_code agencyCode, avy.agency_name agencyName, ")
                .append(" avy.salary_year year,count(smed.id) personCount, ifNull(sum(s1),0) realSalary ")
                .append(" from ")
                .append(TableName.SALARY_THIRTEEN_MONTH).append(" smed ")
                .append(" join ") .append(TableName.THIRTEEN_AGENCY_VERIFY).append(" avy ")
                .append(" on ").append(" smed.agency_id  = avy.agency_id  ")
                .append(" where ")
                .append(" avy.is_deleted = 0  ")
                .append(" and avy.is_end = 0  ")
                .append(" and  ").append(SysUtils.inSql("smed.agency_id", agencyIds));

        if(!CollectionUtils.isEmpty(queryForm)){
            if (StringUtils.isEmpty(queryForm.get("menuid"))){
                sb.append("and avy.is_pay_data_created = 1 ");
            }
            if (!StringUtils.isEmpty(queryForm.get("province"))){
                sb.append(" and avy.mof_div_code = ? ");
                conditions.add(queryForm.get("province"));
            }
            if(!StringUtils.isEmpty(queryForm.get("salaCardBank"))){
                sb.append(" and smed.sala_card_bank=?");
                conditions.add(queryForm.get("salaCardBank"));
            }
            if(!StringUtils.isEmpty(queryForm.get("tabPane"))){
                if("0".equals(queryForm.get("tabPane")+"")){
                    sb.append(" and avy.is_bank_data_created = 0 ");
                }else {
                    sb.append(" and avy.is_bank_data_created = 2 ");
                }
            }
        }
        sb.append(" group by avy.is_bank_data_created , avy.id, avy.agency_id, avy.agency_code, avy.agency_name, avy.salary_year ")
                .append(" order by avy.agency_code ")
                .append(" )t");
        return this.getjdbcTemplate().queryForList(sb.toString(), conditions.toArray());
    }


}
